package Model;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import POJO.CategoryPOJO;



public class CategoryModel {
		public static CategoryPOJO SelectCategoryByID(int ID)
		{
			 Connection con = DataProvider.Connection();
			 CategoryPOJO ca = null;
		        if (con != null){
		            try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("select * from [category] where id=?");
		                ps.setInt(1, ID);
		                ResultSet rs = ps.executeQuery();
		                if(rs.next()) {
		                	ca = new  CategoryPOJO();
		                	ca.SetID(rs.getInt("id"));
		                	ca.SetName(rs.getString("name"));
		                }
		                con.close();
		            } catch (SQLException ex) {
		                return null;
		            }    
		        }
		        return ca;
		}
		public static CategoryPOJO SelectCategoryBySID(String ID)
		{
			 Connection con = DataProvider.Connection();
			 CategoryPOJO ca = null;
		        if (con != null){
		            try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("select * from [category] where id=?");
		                ps.setString(1, ID);
		                ResultSet rs = ps.executeQuery();
		                if(rs.next()) {
		                	ca = new  CategoryPOJO();
		                	ca.SetID(rs.getInt("id"));
		                	ca.SetName(rs.getString("name"));
		                }
		                con.close();
		            } catch (SQLException ex) {
		                return null;
		            }    
		        }
		        return ca;
		}
		public static ArrayList<CategoryPOJO>  SelectCategoryByALL()
		{
			 Connection con = DataProvider.Connection();
			 CategoryPOJO ca = null;
				ArrayList<CategoryPOJO> listcategory = new  ArrayList<CategoryPOJO>();
		        if (con != null){
		            try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("select * from [category]");
		                ResultSet rs = ps.executeQuery();
		                while(rs.next()) {
		                	ca = new  CategoryPOJO();
		                	ca.SetID(rs.getInt("id"));
		                	ca.SetName(rs.getString("name"));
		                	listcategory.add(ca);
		                }
		                con.close();
		            } catch (SQLException ex) {
		                return null;
		            }    
		        }
		        return listcategory;
		}
		public static ArrayList<CategoryPOJO> TimKiem(String Ser) throws UnsupportedEncodingException
		{
			Connection con = DataProvider.Connection();
			 
			 CategoryPOJO ca = null;
				ArrayList<CategoryPOJO> listcategory = new  ArrayList<CategoryPOJO>();
			 if(con!=null)
			 {
		    	  try {
		                PreparedStatement ps;
		                if(Ser != "")
		                {
		               
		                ps  = con.prepareCall("select * from [category] where name Like ? ");
		                byte pname[]=Ser.getBytes("ISO-8859-1");
		                String value1= new String(pname,"UTF-8");
		                value1 = '%' + value1 + '%';
		                ps.setString(1,value1);
		                }
		                else
		                {
		                	 ps  = con.prepareCall("select * from [category] ");
		                }
		                ResultSet rs = ps.executeQuery();
		                while( rs.next()) {
		                	ca = new  CategoryPOJO();
		                	ca.SetID(rs.getInt("id"));
		                	ca.SetName(rs.getString("name"));
		                	listcategory.add(ca);
		                }
		                con.close();
		            } catch (SQLException ex) {
		                return null;
		            }    
			 }
		     return listcategory;
		}
		public static int InsertCategory(String name) throws UnsupportedEncodingException
		{
			int flag=0;
			Connection con = DataProvider.Connection();
			  if (con != null){
		            try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("insert into [category] values(?)");
		                byte ptext[] = name.getBytes("ISO-8859-1"); 
		                String value = new String(ptext, "UTF-8"); 
		                ps.setString(1,value);
		                flag = ps.executeUpdate();  
		                con.close();
		            } catch (SQLException ex) {
		            	ex.printStackTrace();
		            }    
		        }
			return flag;
		}
		public static int UpdateCategory(String name,String id) throws UnsupportedEncodingException
		{
			int flag=0;
			Connection con = DataProvider.Connection();
			  if (con != null){
		            try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("update [category] set [name]=? where id=?");
		                byte ptext[] = name.getBytes("ISO-8859-1"); 
		                String value = new String(ptext, "UTF-8"); 
		                ps.setString(1,value);
		                ps.setString(2,id);
		                flag = ps.executeUpdate();  
		                con.close();
		            } catch (SQLException ex) {
		            	ex.printStackTrace();
		            }    
		        }
			return flag;
		}
		public static int DeleteCategory(int id)
		{
			int flag=0;
			Connection con = DataProvider.Connection();
			if(con!=null)
			{
				  try {
		                PreparedStatement ps;
		                ps  = con.prepareCall("Delete from [category] where id=?");
		                ps.setInt(1, id);
		                flag = ps.executeUpdate();              
		                con.close();
		            } catch (SQLException ex) {
		            	ex.printStackTrace();
		            }    
			}
			return flag;
		}
}
